import ast
import datetime
import json
import math

import pycountry
import pymysql
import requests
from dateutil.relativedelta import relativedelta
import re
import emoji
import string

# 获得国家名中英dict
from flask import request

try:
    with open("./static/resource/jsons/word.json", 'r', encoding='utf-8') as file:
        # 路径和启动文件所在有关，所以这里是./而不是../
        countryMap = json.loads(file.read())
        en2chn = countryMap['namemap']
except:
    with open("../static/resource/jsons/word.json", 'r', encoding='utf-8') as file:
        # 路径和启动文件所在有关，所以这里是./而不是../
        countryMap = json.loads(file.read())
        en2chn = countryMap['namemap']


# 得到所有环比依据
def get_basic_data(basic_key):
    # TODO 这里补全数据库查询代码
    print(basic_key)
    if basic_key == "month":
        basic_datas_str = yuehuanbi()
    elif basic_key == "season":
        basic_datas_str = jihuanbi()
    elif basic_key == "half_year":
        basic_datas_str = bannianhuanbi()
    else:
        basic_datas_str = nianhuanbi()
    print(basic_datas_str[0])
    basic_data_splits = basic_datas_str[0].split(" ")
    basic_datas = [
        {
            'name': '推文',
            'data': {
                'sum': basic_data_splits[0],
                'trend': basic_data_splits[3],
                'unit': '条'
            }
        },
        {
            'name': '点赞',
            'data': {
                'sum': basic_data_splits[1],
                'trend': basic_data_splits[4],
                'unit': '个'
            }
        },
        {
            'name': '国家/地区',
            'data': {
                'sum': basic_data_splits[2],
                'trend': basic_data_splits[5],
                'unit': '个'
            }
        }]
    return basic_datas


def all_sum():
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    sql1 = f"""SELECT COUNT(docID) FROM doc_info;"""  # 总推文
    sql2 = f"""SELECT SUM(like_cnt) FROM doc_info;"""  # 总点赞
    sql3 = f"""SELECT  COUNT(DISTINCT addr) FROM user_info;"""  # 总国家
    try:
        # Execute the SQL command
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        return l1, l2, l3
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


# 【大屏-左上】环比数值受所选时间范围影响
def yuehuanbi():
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # 本月总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)
    sql1 = f"""SELECT 
       count(documentID)
FROM user2doc
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = 3;"""
    sql2 = f"""SELECT 
       sum(like_cnt)
FROM user2doc
JOIN doc_info on doc_info.docID = user2doc.documentID
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = 3;"""
    sql3 = f"""SELECT 
       count(DISTINCT addr)
FROM user2doc
JOIN user_info on user2doc.userID = user_info.user_id
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = 3;"""

    # 上月总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)-1
    sql4 = f"""SELECT 
           count(documentID)
    FROM user2doc
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = 2;"""
    sql5 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = 2;"""
    sql6 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = 2;"""
    cursor.execute(sql1)
    l1 = cursor.fetchall()
    cursor.execute(sql2)
    l2 = cursor.fetchall()
    cursor.execute(sql3)
    l3 = cursor.fetchall()
    cursor.execute(sql4)
    l4 = cursor.fetchall()
    cursor.execute(sql5)
    l5 = cursor.fetchall()
    cursor.execute(sql6)
    l6 = cursor.fetchall()
    per_blog = l1[0][0] / l4[0][0] * 100
    per_like = int(l2[0][0]) / int(l5[0][0]) * 100
    per_nation = l3[0][0] / l6[0][0] * 100
    return '{} {} {} {:.2f} {:.2f} {:.2f}'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like, per_nation),


def jihuanbi():
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # 获得本季度第一天
    today = datetime.date.today()
    quarter_start_day = datetime.date(today.year, today.month - (today.month - 1) % 3, 1)
    start_day = quarter_start_day.isoformat()

    # 获得本季度最后一天
    today = datetime.date.today()
    quarter_end_day = datetime.date(today.year, today.month - (today.month - 1) % 3 + 2, 1) + relativedelta(months=1,
                                                                                                            days=-1)
    end_day = quarter_end_day.isoformat()

    # 上季度第一天和最后一天
    las_start_day = quarter_start_day + relativedelta(months=-3)
    las_end_day = quarter_end_day + relativedelta(months=-3)  # 上两个月，上N个月参数为(months=-N)
    last_start_day = datetime.datetime.strftime(las_start_day, "%Y-%m-%d")
    last_end_day = datetime.datetime.strftime(las_end_day, "%Y-%m-%d")

    # 本季度总推文数
    sql1 = f"""SELECT 
            count(documentID)
            FROM user2doc
            where date between "2023-01-01" and "2023-03-31";"""
    # 本季度总点赞数
    sql2 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between "2023-01-01" and "2023-03-31";"""
    # 本季度总国家数
    sql3 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between "2023-01-01" and "2023-03-31";"""
    # 上季度总推文数
    sql4 = f"""SELECT 
            count(document)
            FROM user2doc
            where date between "2022-10-01" and "2022-12-31";"""
    # 上季度总点赞数
    sql5 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between "2022-10-01" and "2022-12-31";"""
    # 上季度总国家数
    sql6 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between "2022-10-01" and "2022-12-31";"""
    try:
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        cursor.execute(sql4)
        l4 = cursor.fetchall()
        cursor.execute(sql5)
        l5 = cursor.fetchall()
        cursor.execute(sql6)
        l6 = cursor.fetchall()
        per_blog = l1[0][0] / l4[0][0] * 100
        per_like = int(l2[0][0]) / int(l5[0][0]) * 100
        per_nation = l3[0][0] / l6[0][0] * 100
        return '{} {} {} {:.2f} {:.2f} {:.2f}'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like,
                                                      per_nation),
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


def bannianhuanbi():
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # 获得半年第一天和最后一天
    today = datetime.date.today()
    if today.month <= 6:
        quarter_start_day = datetime.date(today.year, 1, 1)
        quarter_end_day = quarter_start_day + relativedelta(months=6)

    else:
        quarter_start_day = datetime.date(today.year, 7, 1)
        quarter_end_day = quarter_start_day + relativedelta(months=6)

        start_day = quarter_start_day.isoformat()
        end_day = quarter_end_day.isoformat()
    # 获得上半年的第一天和最后一天
    las_start_day = quarter_start_day - relativedelta(months=6)
    las_end_day = quarter_end_day - relativedelta(months=6)
    last_start_day = las_start_day.isoformat()
    last_end_day = las_end_day.isoformat()

    # 半年总推文数
    sql1 = f"""SELECT 
            count(documentID)
            FROM user2doc
            where date between "2023-01-01" and "2023-06-30";"""
    # 半年总点赞数
    sql2 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between "2023-01-01" and "2023-06-30";"""
    # 半年总国家数
    sql3 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between "2023-01-01" and "2023-06-30";"""
    # 上半年总推文数
    sql4 = f"""SELECT 
            count(documentID)
            FROM user2doc
            where date between "2022-07-01" and "2022-12-31";"""
    # 上半年总点赞数
    sql5 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between "2022-07-01" and "2022-12-31";"""
    # 上半年总国家数
    sql6 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between "2022-07-01" and "2022-12-31";"""
    try:
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        cursor.execute(sql4)
        l4 = cursor.fetchall()
        cursor.execute(sql5)
        l5 = cursor.fetchall()
        cursor.execute(sql6)
        l6 = cursor.fetchall()
        per_blog = l1[0][0] / l4[0][0] * 100
        per_like = int(l2[0][0]) / int(l5[0][0]) * 100
        per_nation = l3[0][0] / l6[0][0] * 100
        return '{} {} {} {:.2f} {:.2f} {:.2f}'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like,
                                                      per_nation),
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


def nianhuanbi():
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # 本年总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)
    sql1 = f"""SELECT 
       count(documentID)
FROM user2doc
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""
    sql2 = f"""SELECT 
       sum(like_cnt)
FROM user2doc
JOIN doc_info on doc_info.docID = user2doc.documentID
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""
    sql3 = f"""SELECT 
       count(DISTINCT addr)
FROM user2doc
JOIN user_info on user2doc.userID = user_info.user_id
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""

    # 上年总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)-1
    sql4 = f"""SELECT 
           count(documentID)
    FROM user2doc
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    sql5 = f"""SELECT 
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    sql6 = f"""SELECT 
           count(DISTINCT addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    cursor.execute(sql1)
    l1 = cursor.fetchall()
    cursor.execute(sql2)
    l2 = cursor.fetchall()
    cursor.execute(sql3)
    l3 = cursor.fetchall()
    cursor.execute(sql4)
    l4 = cursor.fetchall()
    cursor.execute(sql5)
    l5 = cursor.fetchall()
    cursor.execute(sql6)
    l6 = cursor.fetchall()
    per_blog = l1[0][0] / l4[0][0] * 100
    per_like = int(l2[0][0]) / int(l5[0][0]) * 100
    per_nation = l3[0][0] / l6[0][0] * 100
    return '{} {} {} {:.2f} {:.2f} {:.2f}'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like, per_nation),


# 【大屏-左中】获取情绪统计信息
def get_sentiment_statistic(timeBegin='2000/01/01', timeEnd='3000/01/01'):
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',  # 连接主机, 默认127.0.0.1
        user='select_user',  # 用户名
        passwd='1234',  # 密码
        port=3306,  # 端口，默认为3306
        db='poav_db',  # 数据库名称
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # sql_str = f"select `sentiment`, count(*) from `doc2sentiment` group by `sentiment`;"
    sql_str = f"select `sentiment`, count(*) from `doc2sentiment` left join `user2doc` " \
              f"on doc2sentiment.docID = user2doc.documentID " \
              f"where user2doc.date < '{timeEnd}' and " \
              f"user2doc.date > '{timeBegin}' group by `sentiment`;"
    print(sql_str)
    cursor.execute(sql_str)
    res = []
    for _tuple in cursor.fetchall():
        res.append({'name': _tuple[0], 'value': _tuple[1]})
    db.close()
    return res


# 【大屏-左下】获取情感统计信息
def get_emotion_statistic(timeBegin='2000/01/01', timeEnd='3000/01/01'):
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',  # 连接主机, 默认127.0.0.1
        user='select_user',  # 用户名
        passwd='1234',  # 密码
        port=3306,  # 端口，默认为3306
        db='poav_db',  # 数据库名称
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # sql_str = f"select `emotion`, count(*) from `doc2emotion` group by `emotion` limit 7;"
    sql_str = f"select `emotion`, count(*) from `doc2emotion` left join `user2doc` " \
              f"on doc2emotion.docID = user2doc.documentID " \
              f"where user2doc.date < '{timeEnd}' and " \
              f"user2doc.date > '{timeBegin}' group by `emotion` limit 7;"
    print(sql_str)
    cursor.execute(sql_str)
    res = []
    for _tuple in cursor.fetchall():
        res.append({'name': _tuple[0], 'value': _tuple[1]})
    db.close()
    return res


# 【大屏-中上】地图数据
def region2fullname(region):
    """
    由简称获得全称
    :param region: HKG
    :return: Hong Kong
    """
    try:
        full_name = pycountry.countries.get(alpha_2=region).name
        return full_name
    except:
        try:
            full_name = pycountry.countries.get(alpha_3=region).name
            return full_name
        except:
            return '-'


def get_region_location(location):
    # 香港 -> ('HKG', (114.17612, 22.27683))
    geocode_url = "https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer/findAddressCandidates"
    params = {
        "SingleLine": location,
        "outFields": "Country",
        "f": "json"
    }
    response = requests.get(geocode_url, params=params).json()

    if "candidates" in response and len(response["candidates"]) > 0:
        predict_ = response["candidates"][0]
        country = predict_["attributes"]["Country"]
        location_ = (predict_['location']['x'], predict_['location']['y'])
        return region2fullname(country), location_
    return '-', '-'


# 国家数目统计、地区-ip地址
def get_locations():
    """
    :return: [{'name': 'Japan', 'coord': [139.27149500000007, 36.655226998000046]}, ...]
    """
    name_coords = []
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    query_str = '''
    select addr, location from poav_db.user_info;
    '''
    cursor.execute(query_str)
    res_list = cursor.fetchall()
    for instance in res_list:
        try:
            # 删除字符串中的括号，并用逗号分隔
            processed_str = instance[1].strip('()').split(',')
            # 将字符串元素转换为整数，并组成列表
            result_list = [float(item) for item in processed_str]
            # 添加
            name_coords.append({'name': instance[0], 'coord': result_list})
        except:
            # 会有 ('-','-')这样的数据，直接跳过
            pass

    return name_coords


def get_count_cnt():
    """
    :return: dataAddr = [{'name': '日本', 'value': 1254}, ...]
    """
    dataAddr = []
    locations = get_locations()
    # 使用字典和遍历列表的方式统计每个元素出现的次数
    result_dict = {}
    for instance in locations:
        location = en2chn.get(instance['name'])
        result_dict[location] = result_dict.get(location, 0) + 1
    for countryName in result_dict.keys():
        dataAddr.append({'name': countryName, 'value': result_dict.get(countryName)})
    return dataAddr, locations


# 【大屏-中下】事件趋势数据查询：大屏数据分析中下热点数据
def select_event_date(timeBegin='2000/01/01', timeEnd='3000/01/01'):
    sql_count = f"""
    SELECT count(*) from user2doc where `date` > '{timeBegin}' and `date` < '{timeEnd}' group by `date` order by `date`;
    """
    sql_date = f"""
    SELECT DISTINCT `date` from user2doc where `date` > '{timeBegin}' and `date` < '{timeEnd}' group by `date`; 
    """
    # 链接一波数据库
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',  # 连接主机, 默认127.0.0.1
        user='select_user',  # 用户名
        passwd='1234',  # 密码：本地mysql的数据
        port=3306,  # 端口，默认为3306
        db='poav_db',  # 数据库名称
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()

    try:
        # Execute the SQL command
        cursor.execute(sql_count)
        date_count = cursor.fetchall()
        cursor.execute(sql_date)
        date_list = cursor.fetchall()
        event_data = {
            'xAxis': [str(date[0]) for date in date_list],
            'yAxis': [int(count[0]) for count in date_count]
        }
        db.close()
        return event_data
    except Exception as e:
        # Rollback in case there is any error回滚
        db.rollback()
        db.close()
        print(e)
        print(sql_date)
        print(sql_count)
        pass


# 大屏数据分析中下热点数据
def get_event_data(beginDate, endDate):
    # 考虑一下预处理
    return select_event_date(beginDate, endDate)


# 【大屏-右侧】查询推文数据：大屏分析右侧的列表
def read_data(pageSize, pageIdx, orderBy):
    pageSize = int(pageSize)
    pageIdx = int(pageIdx)
    offset = pageIdx * pageSize
    sql = f"""SELECT DISTINCT user_info.user_name,user_info.fans_cnt,user_info.addr,
    user2doc.document, user2doc.date, user2doc.create_time,
    doc_info.like_cnt, doc_info.comment_cnt, doc_info.transfer_cnt, doc_info.share_cnt
    FROM doc_info
    JOIN user2doc on doc_info.docID = user2doc.documentID
    JOIN user_info on user2doc.userID = user_info.user_id
    order by doc_info.{orderBy} DESC   
    limit {offset},{pageSize};"""

    # 链接一波数据库
    # Open database connection
    db = pymysql.connect(
        host='118.89.122.209',  # 连接主机, 默认127.0.0.1
        user='select_user',  # 用户名
        passwd='1234',  # 密码：本地mysql的数据
        port=3306,  # 端口，默认为3306
        db='poav_db',  # 数据库名称
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()

    try:
        # Execute the SQL command
        cursor.execute(sql)
        l = cursor.fetchall()
        list = []
        for j in range(pageSize):
            # 将查询结果转换为字典
            row_dict = {
                'userName': l[j][0],
                'fans_cnt': l[j][1],
                'addr': l[j][2],
                'document': l[j][3],
                'date': l[j][4],
                'create_time': l[j][5],
                'like_cnt': l[j][6],
                'comment_cnt': l[j][7],
                'transfer_cnt': l[j][8],
                'share_cnt': l[j][9],
                # 添加其他列及其对应的值
            }
            list.append(row_dict)
        db.close()
        return list
    except Exception as e:
        # Rollback in case there is any error回滚
        db.rollback()
        db.close()
        print(e)
        print(sql)
        pass


# 查询推文数据&格式化
def get_blogs_data(pageSize, pageNum, orderBy):
    blog_list = read_data(pageSize, pageNum, orderBy)
    for instance in blog_list:
        instance['date'] = str(instance.get('date'))

        delta = instance.get('create_time')
        # 提取小时、分钟和秒数
        hours, remainder = divmod(delta.seconds, 3600)
        minutes, seconds = divmod(remainder, 60)
        # 格式化为 hh:mm:ss
        formatted_time_str = f"{hours:02d}:{minutes:02d}:{seconds:02d}"
        instance['time'] = formatted_time_str
        instance['create_time'] = formatted_time_str
        instance['blog_ctn'] = instance['document']
        instance['nation'] = instance['addr']
    return blog_list


# 【大屏-右侧+日期】时间对应list：点选日期之后根据日期选择合适的推文列表
def read_data_with_date(pageSize, pageIdx, orderBy, date):  # year,month,day
    # date = datetime.date(year, month, day)
    # date= date.isoformat()
    # date1 = datetime.datetime.strftime(date, "%Y-%m-%d")
    pageSize = int(pageSize)
    pageIdx = int(pageIdx)
    offset = pageIdx * pageSize
    # orderBy=clean(orderBy)
    sql = f"""SELECT DISTINCT user_info.user_name,user_info.fans_cnt,user_info.addr,
    user2doc.document, user2doc.date, user2doc.create_time,
    doc_info.like_cnt, doc_info.comment_cnt,
    doc_info.transfer_cnt, doc_info.share_cnt
    FROM doc_info
    JOIN user2doc on doc_info.docID = user2doc.documentID
    JOIN user_info on user2doc.userID = user_info.user_id
    where date = %s
    order by doc_info.{orderBy} DESC   
    limit {offset},{pageSize};"""

    try:
        # 链接一波数据库
        # Open database connection
        db = pymysql.connect(
            host='118.89.122.209',  # 连接主机, 默认127.0.0.1
            user='select_user',  # 用户名
            passwd='1234',  # 密码：本地mysql的数据
            port=3306,  # 端口，默认为3306
            db='poav_db',  # 数据库名称
            # charset='utf8'  # 字符编码
        )
        # prepare a cursor object using cursor() method
        cursor = db.cursor()
        # Execute the SQL command
        cursor.execute(sql, (date,))
        l = cursor.fetchall()
        list = []
        for j in range(pageSize):
            # 将查询结果转换为字典
            row_dict = {
                'userName': l[j][0],
                'fans_cnt': l[j][1],
                'addr': l[j][2],
                'document': l[j][3],
                'date': l[j][4],
                'create_time': l[j][5],
                'like_cnt': l[j][6],
                'comment_cnt': l[j][7],
                'transfer_cnt': l[j][8],
                'share_cnt': l[j][9],
                # 添加其他列及其对应的值
            }
            list.append(row_dict)
        db.close()
        return list
    except Exception as e:
        print(e)
        print(sql)
        # Rollback in case there is any error回滚
        db.rollback()
        db.close()
        pass


# 时间对应list&格式化
def get_data_with_date(pageSize, pageIdx, orderBy, date):
    blog_list = read_data_with_date(pageSize, pageIdx, orderBy, date)
    for instance in blog_list:
        instance['date'] = str(instance.get('date'))
        delta = instance.get('create_time')
        # 提取小时、分钟和秒数
        hours, remainder = divmod(delta.seconds, 3600)
        minutes, seconds = divmod(remainder, 60)
        # 格式化为 hh:mm:ss
        formatted_time_str = f"{hours:02d}:{minutes:02d}:{seconds:02d}"
        instance['time'] = formatted_time_str
        instance['create_time'] = formatted_time_str
        instance['blog_ctn'] = instance['document']
        instance['nation'] = instance['addr']
    print(blog_list)
    return blog_list


def convert_to_dict_list(data):
    return [{'name': item[0], 'value': item[1]} for item in data]


def select_document():
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    sql1 = f"""SELECT ud.document
                FROM user2doc ud;   
            """
    try:
        # Execute the SQL command
        cursor.execute(sql1)
        results = cursor.fetchall()
        # print(results)
        text_list = [emoji.demojize(p.clean(i[0])) for i in results]
        # print(text_list)
        text = ' '.join(text_list)  # 将文本列表合并为一个字符串
        text = re.sub('[\d]', '', text)  # 去除数字
        # text = text.lower() # 转为小写
        tokens = nltk.word_tokenize(text)  # 分词
        stop_words = set(stopwords.words('english'))  # 去除停用词
        punctuations = set(string.punctuation)  # 英文标点元组
        filtered_tokens = [token for token in tokens if token not in stop_words and token not in punctuations]
        # 词形还原
        lemmatizer = WordNetLemmatizer()
        lemmatized_tokens = [lemmatizer.lemmatize(token) for token in filtered_tokens]
        # 计算词频
        word_freq = nltk.FreqDist(lemmatized_tokens)
        return convert_to_dict_list(word_freq.most_common(500))
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()


def select_tweet(key):
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    sql1 = f"""SELECT ud.document
                FROM user2doc ud;
                WHERE ud.document LIKE '%{key}%'
                LIMIT 10;   
            """
    try:
        # Execute the SQL command
        cursor.execute(sql1)
        results = cursor.fetchall()
        tweet = [{"tweet": i} for i in results]
        return {"result": tweet}
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


# 查询意见领袖数据
def select_tci():
    db = pymysql.connect(
        host='118.89.122.209',
        port=3306,
        user='select_user',
        passwd='1234',
        db='poav_db',
        # cursorclass= pymysql.cursors.DictCursor
        # charset='utf8'  # 字符编码
    )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    sql1 = f"""SELECT ui.user_id, ui.user_name,
                    COUNT(di.docID) AS total_posts,
                    ui.fans_cnt,
                    ui.like_cnt,
                    AVG(di.comment_cnt) AS avg_comment_cnt,
                    AVG(di.share_cnt) AS avg_share_cnt,
                    AVG(di.transfer_cnt) AS avg_transfer_cnt,
                    ui.location
                FROM user_info ui
                JOIN user2doc u2d ON ui.user_id = u2d.userID
                JOIN doc_info di ON u2d.documentID = di.docID
                GROUP BY ui.user_id, ui.user_name, ui.fans_cnt, ui.like_cnt, ui.location;

            """
    try:
        # Execute the SQL command
        cursor.execute(sql1)
        l1 = cursor.fetchall()

        data = []
        for row in l1:
            data.append({
                "user_id": row[0],
                "user_name": row[1],
                "total_post": row[2],
                "fans_cnt": row[3],
                "like_cnt": row[4],
                "av_comment": float(row[5]),
                "av_share": float(row[6]),
                "av_trans": float(row[7]),
                "location": row[8]
            })
        tcis = []
        for da in data:
            x1 = da["total_post"]
            x2 = da["fans_cnt"]
            x3 = da["like_cnt"]
            if x1 <= 0 and x2 > 0:
                w1 = math.log(x2)
            elif x1 > 0 and x2 <= 0:
                w1 = math.log(x1)
            elif x1 > 0 and x2 > 0:
                w1 = 0.7 * math.log(x1) + 0.3 * math.log(x2)
            else:
                w1 = 0
            if x3 > 0:
                w2 = 0.1 * math.log(x3) + 0.3 * da["av_comment"] * da["total_post"] + 0.3 * da["av_share"] * da[
                    "total_post"] + 0.3 * da["av_trans"] * da["total_post"]
            else:
                w2 = 1 / 3 * da["av_comment"] * da["total_post"] + 1 / 3 * da["av_share"] * da["total_post"] + 1 / 3 * \
                     da["av_trans"] * da["total_post"]
            tci = (0.2 * w1 + 0.8 * w2) * 160
            tcis.append({
                "tci": tci,
                "user_id": da["user_id"],
                "user_name": da["user_name"],
                "fans_cnt": da["fans_cnt"],
                "location": da["location"]
            })
        sorted_tci = sorted(tcis, key=lambda x: x["tci"], reverse=True)
        print(len(sorted_tci))
        yjlx = []
        for sc in sorted_tci[:20]:
            user_id = sc["user_id"]
            sql2 = """
            SELECT di.docID, di.like_cnt, di.comment_cnt, di.transfer_cnt, di.share_cnt, di.visit_cnt, ud.document,ud.date,ud.create_time
            FROM doc_info di
            JOIN user2doc ud ON di.docID = ud.documentID
            JOIN user_info ui ON ud.userID = ui.user_id
            WHERE ui.user_id = %s
            ORDER BY di.like_cnt DESC, di.docID, di.comment_cnt, di.transfer_cnt, di.share_cnt, di.visit_cnt,ud.date,ud.create_time
            LIMIT 5;
            """
            try:
                # Execute the SQL command
                cursor.execute(sql2, (user_id))
                l2 = cursor.fetchall()
                for row in l2:
                    yjlx.append({
                        "user_name": sc["user_name"],
                        "fans_cnt": sc["fans_cnt"],
                        "location": sc["location"],
                        "doc_id": row[0],
                        "like_cnt": row[1],
                        "comment_cnt": row[2],
                        "trans_cnt": row[3],
                        "share_cnt": row[4],
                        "visit_cnt": row[5],
                        "tweet": row[6],
                        "date": str(row[7]),
                        "time": str(row[8])
                    })
            except Exception as e:
                print(e)
                # Rollback in case there is any error回滚
                db.rollback()
                pass
        print(yjlx)
        return {"result": yjlx}
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


if __name__ == '__main__':
    # res = get_locations()
    # print(en2chn.get("Japan"))
    # print(get_region_location('香港'))
    # print(region2country('HKG'))
    get_count_cnt()
